package com.yuantu.judiciary.dao;

import com.yuantu.judiciary.common.SysOnOffState;
import com.yuantu.judiciary.model.domain.AccountDO;
import com.yuantu.judiciary.model.enums.EndpointType;
import com.yuantu.judiciary.model.enums.OrganizationType;
import com.yuantu.judiciary.model.enums.UserType;
import com.yuantu.judiciary.model.vo.AccountSimpleVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.sql.Timestamp;
import java.util.Collection;
import java.util.List;

/**
 * 账号数据库操作
 *
 * @author syw
 * @since 2023-08-03
 **/
public interface IAccountDao extends JpaRepository<AccountDO, Long> {

    AccountDO findByIdAndDeletedFalse(Long id);

    AccountDO findByOpenIdAndDeletedFalse(String openId);

    AccountDO findFirstByUserIdAndDeletedFalse(Long userId);

    List<AccountDO> findAccountByUsernameAndTypeAndStateAndDeletedFalse(String username, UserType type, SysOnOffState state);

    @Query("select a from AccountDO a " +
            "where (:username is null or :username = '' or a.username like concat('%', :username, '%')) " +
            "and a.type = :type and a.deleted = false ")
    Page<AccountDO> getAccountByUsernameLikeAndTypeAndDeletedFalse(String username, UserType type, Pageable pageable);

    @Modifying
    @Query("update AccountDO set deleted = true, modifierId = :accountId, modifyTime = :currentTime " +
            "where ((coalesce (:id) is null and 1=0) or (coalesce (:id) is not null and id in (:id))) " +
            "and deleted = false ")
    void deleteByIdIn(List<Long> id, Long accountId, Timestamp currentTime);

    List<AccountDO> findByUserIdAndTypeInAndDeletedFalse(Long userId, List<UserType> types);

    @Query("select a.username as username, u.phone as phone, a.type as type from AccountDO a " +
            "left join UserInfoDO u on a.userId = u.id " +
            "where a.organizationId = :organizationId and a.deleted = false and u.deleted = false " +
            "and (:username is null or :username = '' or a.username like concat('%', :username, '%')) " +
            "and (:phone is null or :phone = '' or u.phone like concat('%', :phone, '%')) ")
    Page<SimpleAccountInfo> getSimpleAccountInfoByUsernameAndPhone(Long organizationId, String username, String phone, Pageable pageable);

    @Query("select a from AccountDO a where a.deleted = false ")
    List<AccountDO> findAllInfos();

    @Query("select a.id as accountId, u.userName as userName, u.phone as phone, a.organizationId as organizationId, " +
            "a.historyScore as historyScore, a.membershipRankId as membershipRankId from AccountDO a left join UserInfoDO u on a.userId = u.id " +
            "where a.deleted = false and u.deleted = false and a.origin != 'PLATFORM' " +
            "and (:accountInfo is null or :accountInfo = '' or (u.userName like concat('%', :accountInfo, '%') or u.phone like concat('%', :accountInfo, '%'))) " +
            "order by a.historyScore desc ")
    Page<SimpleAccountInfo> findInfoByUserNameOrPhone(String accountInfo, Pageable pageable);

    @Query("select a from AccountDO a where a.deleted = false and a.origin = :origin " +
            "and (:userName is null or :userName = '' or (a.username like concat('%', :userName, '%')))  ")
    List<AccountDO> findInfosByUserNameAndOriginAndDeletedFalse(String userName, EndpointType origin);


    interface SimpleAccountInfo {

        Long getAccountId();

        Long getMembershipRankId();

        Double getHistoryScore();

        Long getOrganizationId();

        String getUsername();

        String getPhone();

        UserType getType();
    }

    Integer countDistinctByUserIdAndOrganizationIdAndDeletedFalse(Long userId, Long organizationId);

    List<AccountDO> findAccountByOrganizationIdAndDeletedFalse(Long organizationId);

    @Query("select a.id as accountId, a.createTime as createTime, o.type as organizationType " +
            "from AccountDO a " +
            "left join OrganizationDO o on a.organizationId = o.id " +
            "where a.deleted = false and o.deleted = false " +
            "and o.id is not null " +
            "and (:startTime is null or a.createTime >= :startTime) " +
            "and (:endTime is null or a.createTime <= :endTime) ")
    List<AccountInfo> getVerifyAccount(Timestamp startTime, Timestamp endTime);

    interface AccountInfo {
        Long getAccountId();

        Timestamp getCreateTime();

        OrganizationType getOrganizationType();
    }

    @Query("select a.id as accountId, a.username as username, u.phone as phone, a.organizationId as organizationId, a.enterName as enterName, a.createTime as createTime " +
            "from AccountDO a " +
            "left join UserInfoDO u on a.userId = u.id " +
            "where a.deleted = false and u.deleted = false " +
            "and (:phone is null or :phone = '' or u.phone like concat('%', :phone, '%')) " +
            "and a.type in (:types) ")
    Page<AccountUserInfo> searchAccountInfo(String phone, List<UserType> types, Pageable pageable);

    @Query("select a.id as accountId, a.username as username, u.phone as phone, a.organizationId as organizationId, a.enterName as enterName, a.createTime as createTime " +
            "from AccountDO a " +
            "left join UserInfoDO u on a.userId = u.id " +
            "where a.deleted = false and u.deleted = false " +
            "and (:phone is null or :phone = '' or u.phone like concat('%', :phone, '%')) " +
            "and a.type in (:types) ")
    List<AccountUserInfo> searchAccountInfo(String phone, List<UserType> types);

    @Query("select a.id as accountId, a.username as username, u.phone as phone, a.organizationId as organizationId, a.enterName as enterName, a.createTime as createTime  " +
            "from AccountDO a " +
            "left join UserInfoDO u on a.userId = u.id " +
            "where a.deleted = false and u.deleted = false " +
            "and a.id in (:id) ")
    Page<AccountUserInfo> getAccountInfo(List<Long> id, Pageable pageable);

    @Query("select a.id as accountId, a.username as username, u.phone as phone, a.organizationId as organizationId, a.enterName as enterName," +
            "a.createTime as createTime, a.membershipRankId as membershipRankId  " +
            "from AccountDO a " +
            "left join UserInfoDO u on a.userId = u.id " +
            "where a.deleted = false and u.deleted = false " +
            "and a.id in (:id) ")
    List<AccountUserInfo> getAccountInfo(List<Long> id);

    interface AccountUserInfo {
        Long getAccountId();

        String getUsername();

        String getPhone();

        Long getOrganizationId();

        String getEnterName();

        Timestamp getCreateTime();

        Long getMembershipRankId();
    }

    @Query("select a " +
            "from AccountDO a " +
            "left join OrganizationDO o on a.organizationId = o.id " +
            "where a.deleted = false and o.deleted = false " +
            "and a.userId = :userId and a.type in (:types) " +
            "and (a.organizationId is null or o.type = :orgType) ")
    List<AccountDO> getSingleAccount(Long userId, List<UserType> types, OrganizationType orgType);


}
